package com.nopay.cdxphone.utils;

import android.text.TextUtils;

import com.blankj.utilcode.util.LogUtils;
import com.nopay.cdxphone.exception.FileNotSupportException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeSet;

import com.nopay.cdxphone.bean.PersonInfoBean;

/**
 * @ProjectName: myVisualPhone
 * @Package: cn.cn.myvisualphone.utils
 * @ClassName: ExcelUtils
 * @Description: java类作用描述
 * @Author: anqi
 * @CreateDate: 2021/6/1 16:58
 * @UpdateUser: 更新者
 * @UpdateDate: 2021/6/1 16:58
 * @UpdateRemark: 更新说明
 * @Version: 1.0
 */
public class ExcelUtils {

    public static List<PersonInfoBean> parseExcel(String filePath) throws FileNotSupportException {
        String errMsg = "";
        List<PersonInfoBean> listNew = null;
        File file = null;
        try {
            file = new File(filePath);
        } catch (NullPointerException e) {
            e.printStackTrace();
        }
        try {
            if (!file.exists()) {
                throw new FileNotSupportException("文件不存在");
            }
        } catch (SecurityException e) {
            errMsg = e.getMessage();
            e.printStackTrace();
            throw new FileNotSupportException(errMsg);

        }

        List<PersonInfoBean> infoBeanList = new ArrayList<>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            int rowsCount = sheet.getPhysicalNumberOfRows();
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            for (int r = 0; r < rowsCount; r++) {
                XSSFRow row = sheet.getRow(r);
                int cellsCount = row.getPhysicalNumberOfCells();
                PersonInfoBean infoBean = null;
                if (0 != r) {
                    infoBean = new PersonInfoBean();
                }
                for (int c = 0; c < cellsCount; c++) {
                    String value = getCellAsString(row, c, formulaEvaluator);
                    String cellInfo = "r:" + r + "; c:" + c + "; v:" + value;
                    if (0 == r) {
                        if (0 == c) {
                            if (!"公司名称".equals(value)) {
                                throw new FileNotSupportException("文件格式错误");
                            }

                        }
                        if (1 == c) {
                            if (!"名字".equals(value)) {
                                throw new FileNotSupportException("文件格式错误");
                            }
                        }
                        if (2 == c) {
                            if (!"电话".equals(value)) {
                                throw new FileNotSupportException("文件格式错误");
                            }
                        }
                    } else {
                        if (0 == c) {
                            infoBean.setCompanyName(value);
                        }

                        if (1 == c) {
                            infoBean.setPersonName(value);
                        }

                        if (2 == c) {
                            infoBean.setTel(value);
                        }

                    }

                }
                if (infoBean != null) {
                    infoBeanList.add(infoBean);
                }

            }
            LogUtils.e("anqi" + infoBeanList);
            listNew = new ArrayList<>(new TreeSet<>(infoBeanList));
            LogUtils.e("anqi" + listNew);

        } catch (InvalidFormatException | IOException | IllegalArgumentException e) {
            e.printStackTrace();
            throw new FileNotSupportException(e.getMessage());
        }
        return listNew;
    }

    private static String getCellAsString(Row row, int c, FormulaEvaluator formulaEvaluator) throws FileNotSupportException {
        String errMsg = "";
        String value = "";
        try {
            Cell cell = row.getCell(c);
            CellValue cellValue = formulaEvaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = "" + cellValue.getBooleanValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    String numericValue = DoubleUtils.double2Str(cellValue.getNumberValue());
                    value = "" + numericValue;
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = "" + cellValue.getStringValue();
                    break;
                default:
                    break;
            }
        } catch (NullPointerException e) {
            errMsg = e.getMessage();
            /* proper error handling should be here */
            LogUtils.e("anqi" + e.getMessage());

        } finally {
            if (!TextUtils.isEmpty(errMsg)){
                throw new FileNotSupportException("文件格式错误");
            }else{
                return value;
            }

        }

    }

}
